/*
 *此javaCode由YMS-JDBC替换工具生成，原mapper位置com.yonyou.ucf.mdf.app.isvdxq.firstpart.pandian.mapper.CheckstockMapper
 */
package com.yonyou.ucf.mdf.app.isvdxq.firstpart.pandian.mapper;

import com.yonyou.iuap.yms.api.IYmsJdbcApi;
import com.yonyou.iuap.yms.param.ResultSetProcessor;
import com.yonyou.iuap.yms.param.SQLParameter;
import com.yonyou.iuap.yms.processor.AutoBeanListProcessor;
import com.yonyou.iuap.yms.processor.AutoBeanProcessor;
import com.yonyou.iuap.yms.processor.ColumnProcessor;
import com.yonyou.iuap.yms.replace.CollectionUtil;
import com.yonyou.ucf.mdf.app.isvdxq.firstpart.pandian.model.CheckProductDto;
import com.yonyou.ucf.mdf.app.isvdxq.firstpart.pandian.model.OtherInAndOut.CheckResultOtherInAndOutDto;
import com.yonyou.ucf.mdf.app.isvdxq.firstpart.pandian.model.checkStockListDto;
import com.yonyou.ucf.mdf.app.isvdxq.firstpart.pandian.model.checkstockMainOrder;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.Iterator;
import java.util.List;

@Repository
public class CheckstockMapperImpl {

    @Autowired
    private IYmsJdbcApi ymsJdbcApi;

    public int GetCheckListTotal(HashMap<String,Object> map) {
        SQLParameter parameter = new SQLParameter(true);
        StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append(" select count(*) from ( ");
		sqlBuilder.append(" select c.* ");
		sqlBuilder.append(" from dxq_checkstock as c ");
		sqlBuilder.append(" inner join dxq_checkstockarea c2 on c.id = c2.dxq_checkstock_id ");
		sqlBuilder.append(" inner join dxqyy_warehouse as w on c.warehouseId = w.id ");
		sqlBuilder.append(" where 1=1 ");
		if (CollectionUtil.isNotEmpty(map.get("locationIds"))) {
			sqlBuilder.append(" and c2.locationID IN ");
			sqlBuilder.append(" ( ");
			int index = 0;
			String separator0 = " , ";
			//迭代器强转风险！
			Iterator<?> iterableIterator = ((Iterable<?>) map.get("locationIds")).iterator();
			while (iterableIterator.hasNext()) {
				Object id = iterableIterator.next();
				sqlBuilder.append(" ? ");
				parameter.addParam(id);
				if (iterableIterator.hasNext()) {
					sqlBuilder.append(separator0);
				}
				index++;
			}
			sqlBuilder.append(" ) ");
		}

		if (map.get("checkCode") != null && !"".equals(map.get("checkCode"))) {
			sqlBuilder.append(" and c.cCheckCode like concat('%',?,'%') ");
			parameter.addParam(map.get("checkCode"));
		}

		if (CollectionUtil.isNotEmpty(map.get("warehouseIds"))) {
			sqlBuilder.append(" and c.warehouseId IN ");
			sqlBuilder.append(" ( ");
			int index = 0;
			String separator1 = " , ";
			//迭代器强转风险！
			Iterator<?> iterableIterator = ((Iterable<?>) map.get("warehouseIds")).iterator();
			while (iterableIterator.hasNext()) {
				Object orgId = iterableIterator.next();
				sqlBuilder.append(" ? ");
				parameter.addParam(orgId);
				if (iterableIterator.hasNext()) {
					sqlBuilder.append(separator1);
				}
				index++;
			}
			sqlBuilder.append(" ) ");
		}

		if (map.get("warehouseId") != null && !"".equals(map.get("warehouseId"))) {
			sqlBuilder.append(" and c.warehouseId = ? ");
			parameter.addParam(map.get("warehouseId"));
		}

		sqlBuilder.append(" and c.iStatus IN (0, 1) ");
		sqlBuilder.append(" and c.dr = 0 ");

		if (map.get("checkType") != null && !"".equals(map.get("checkType"))) {
			sqlBuilder.append(" and c.checkType = ? ");
			parameter.addParam(map.get("checkType"));
		}

		sqlBuilder.append(" group by c.id ");
		sqlBuilder.append(" order by c.id ");
		sqlBuilder.append(" ) as `cc2*` ");

        
        String sql = sqlBuilder.toString();
        ResultSetProcessor processor = new ColumnProcessor(Integer.class);
        return (int) ymsJdbcApi.queryForObject(sql, parameter, processor);
    }


    public checkstockMainOrder GetCheckStockById(String checkId, String tenant_id) {
        SQLParameter parameter = new SQLParameter(true);
        
        String sql = "select * from  dxq_checkstock where id = ? and ytenant_id=? and dr=0\n";
        parameter.addParam(checkId);
		parameter.addParam(tenant_id);

        ResultSetProcessor processor = new AutoBeanProcessor(new checkstockMainOrder());
        return (checkstockMainOrder) ymsJdbcApi.queryForObject(sql, parameter, processor);
    }


    public int InsertcheckStock(String checkType, String isFupan, String checkNo, String cCheckCode, String cRemark, String sourceType, String userid,
								String userid_operator, String warehousename, String warehouseId, String tenant_id, String pk_temp, Integer version,
								String creator, String modifier, String id, String org_id) throws Exception {
        SQLParameter parameter = new SQLParameter(true);

		//checkType:盘点类型(0自由盘点1任务盘点);IsFupan:是否是重复盘点单 1主单 2复盘单;ShenheStatus:审核状态 0开立 1审核通过 2审核不通过已创建复盘;
		//iStatus:单据状态(0未盘点，1盘点中2完成3中途作废4作废5已创建复盘单-1删除);

        String sql = "INSERT INTO `dxq_checkstock`(dr, ShenheStatus, `create_time`,`modify_time`," +
					 "`iStatus`, checkType, IsFupan, `checkNo`,`cCheckCode`,`cRemark`,`sourceType`,`userid`,`userid_operator`,`warehousename`,`warehouseId`,\n" +
			         "`tenant_id`,`ytenant_id`,`pk_temp`,`version`,`creator`,modifier,`id`,org_id)  \n" +
			         "VALUES(0,0,now(),now()," +
					 "?,?,?,?,?,?,?,?,?,?,?,           \n" +
			         "?,?,?,?,?,?,?,?)         \n";
		if(checkType.equals("0")){ //自由盘点
			parameter.addParam("1"); //iStatus:1 盘点中
		}else if(checkType.equals("1")){ //任务盘点
			parameter.addParam("0"); //iStatus:0 未盘点
		}
		parameter.addParam(checkType);
		parameter.addParam(isFupan);
        parameter.addParam(checkNo);
		parameter.addParam(cCheckCode);
		parameter.addParam(cRemark);
		parameter.addParam(sourceType);
		parameter.addParam(userid);
		parameter.addParam(userid_operator);
		parameter.addParam(warehousename);
		parameter.addParam(warehouseId);

		parameter.addParam(tenant_id);
		parameter.addParam(tenant_id);
		parameter.addParam(pk_temp);
		parameter.addParam(version);
		parameter.addParam(creator);
		parameter.addParam(modifier);
		parameter.addParam(id);
		parameter.addParam(org_id);

        
        return ymsJdbcApi.update(sql, parameter);
    }


    public int GetCheckListForLocationToTotal(String checkCode, String locationID, String checkTypeFilter, String tenant_id) {
        SQLParameter parameter = new SQLParameter(true);
        
        String sql = "select count(*) from  (SELECT Count(*) from dxq_checkstock as c inner join dxq_checkstockarea c2 on c.id = c2.dxq_checkstock_id where c2.locationID like ? and c.ytenant_id=? and  c.cCheckCode like ? and c.checkType like ? and c.iStatus IN (0,1)  and c.dr=0 group by c.id) as t ;\n";
        parameter.addParam(locationID);
		parameter.addParam(tenant_id);
		parameter.addParam(checkCode);
		parameter.addParam(checkTypeFilter);

        ResultSetProcessor processor = new ColumnProcessor(int.class);
        return (int) ymsJdbcApi.queryForObject(sql, parameter, processor);
    }


    public List<CheckResultOtherInAndOutDto> OtherInOrOutOrderByCheckStockIncome(List<String> Ids, int type, String tenant_id) {
        SQLParameter parameter = new SQLParameter(true);
        StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append(" SELECT cd.id AS checkDiffId, ");
		sqlBuilder.append(" w.warehouseName AS warehouseName, ");
		sqlBuilder.append(" w.code AS warehouseCode, ");
		sqlBuilder.append(" w.org as orgId, ");
		sqlBuilder.append(" ou.accountOrg as accountOrg, ");
		sqlBuilder.append(" p.code AS stockCode, ");
		sqlBuilder.append(" p.id AS stockId, ");
		sqlBuilder.append(" c.product_sku_id AS stockSkuId, ");
		sqlBuilder.append(" u.code AS unitCode, ");
		sqlBuilder.append(" u.id AS unitId, ");
		sqlBuilder.append(" p.stockUnit AS stockUnit, ");
		sqlBuilder.append(" c.f_stock_count AS stockCount, ");
		sqlBuilder.append(" c.rfid_code as epccode, ");
		sqlBuilder.append(" cd.LabelCount as labelCount, ");
		sqlBuilder.append(" c.batchno AS batchno, ");
		sqlBuilder.append(" c.productDate as productDate, ");
		sqlBuilder.append(" c.invalidDate as invalidDate, ");
		sqlBuilder.append(" u2.unitExchangeType AS unitExchangeType, ");
		sqlBuilder.append(" u2.assistUnit as assistUnitId, ");
		sqlBuilder.append(" u2.mainUnitCount as mainUnitCount ");
		sqlBuilder.append("  ");
		sqlBuilder.append(" FROM dxq_checkdiff cd ");
		sqlBuilder.append(" LEFT JOIN dxq_checkresult cr ON cd.checkResultID = cr.id ");
		sqlBuilder.append(" INNER JOIN dxq_location dl ON dl.id = cr.checkLocationID ");
		sqlBuilder.append(" INNER JOIN dxqyy_warehouse w ON dl.warehouse_id = w.id ");
		sqlBuilder.append(" INNER JOIN dxq_coderfid c ON c.id = cd.coderfid ");
		sqlBuilder.append(" INNER JOIN dxqyy_product p ON c.product_id = p.id ");
		sqlBuilder.append(" INNER JOIN dxqyy_unit u ON p.unit = u.id ");
		sqlBuilder.append(" left JOIN dxqyy_unitassist u2 ON p.id = u2.productId ");
		sqlBuilder.append(" inner JOIN dxqyy_orgunit as ou ON w.org = ou.id ");
		sqlBuilder.append("  ");
		sqlBuilder.append(" WHERE cd.checkstatus = ? ");
		parameter.addParam(type);
		if (CollectionUtil.isNotEmpty(Ids)) {
			sqlBuilder.append(" AND cd.id IN ");
			sqlBuilder.append(" ( ");
			String separator0 = " , ";
			for (int index = 0; index < Ids.size(); index++) {
				String id = Ids.get(index);
				sqlBuilder.append(" ? ");
				parameter.addParam(id);
				if (index < Ids.size() - 1) {
					sqlBuilder.append(separator0);
				}
			}
			sqlBuilder.append(" ) ");
		}
		sqlBuilder.append(" and cd.ytenant_id=? ");
		parameter.addParam(tenant_id);
		sqlBuilder.append(" GROUP BY cd.id; ");

        
        String sql = sqlBuilder.toString();
        ResultSetProcessor processor = new AutoBeanListProcessor(new CheckResultOtherInAndOutDto());
        return (List<CheckResultOtherInAndOutDto>) ymsJdbcApi.queryForList(sql, parameter, processor);
    }


    public int UpdatCheckStatus(String checkID, String modifier, String iStatus, String tenant_id) throws Exception {
        SQLParameter parameter = new SQLParameter(true);
        
        String sql = "update dxq_checkstock set iStatus=?, modify_time=now(),modifier=? where id=? and ytenant_id=? and `dr`=0;\n";
        parameter.addParam(iStatus);
		parameter.addParam(modifier);
		parameter.addParam(checkID);
		parameter.addParam(tenant_id);

        
        return ymsJdbcApi.update(sql, parameter);
    }

	public int UpdatSheheStatus(String checkID, String sheheStatus, String modifier, String tenant_id) throws Exception {
		SQLParameter parameter = new SQLParameter(true);
		//ShenheStatus:审核状态 0开立 1审核通过 2审核不通过已创建复盘
		String sql = "update dxq_checkstock set ShenheStatus=?, ShenheUser=?, Shenhetime=now(), modify_time=now(), modifier=? where id=? and ytenant_id=? and `dr`=0;\n";
		parameter.addParam(sheheStatus); //审核状态
		parameter.addParam(modifier); //审核人
		parameter.addParam(modifier); //修改人
		parameter.addParam(checkID); //盘点单ID
		parameter.addParam(tenant_id); //租户ID


		return ymsJdbcApi.update(sql, parameter);
	}


    public List<checkstockMainOrder> GetCheckListForLocation(String checkCode, String locationID, String checkTypeFilter, int page, int pageSize, String tenant_id) {
        SQLParameter parameter = new SQLParameter(true);
        
        String sql = "SELECT c.* from dxq_checkstock as c inner join dxq_checkstockarea c2 on c.id = c2.dxq_checkstock_id where c2.locationID like ?  and c.ytenant_id=? and  c.cCheckCode like ? and c.checkType like ? and c.iStatus IN (0,1) group by c.id order by c.id limit ?,?;\n";
        parameter.addParam(locationID);
		parameter.addParam(tenant_id);
		parameter.addParam(checkCode);
		parameter.addParam(checkTypeFilter);
		parameter.addParam(page);
		parameter.addParam(pageSize);

        ResultSetProcessor processor = new AutoBeanListProcessor(new checkstockMainOrder());
        return (List<checkstockMainOrder>) ymsJdbcApi.queryForList(sql, parameter, processor);
    }


    public List<CheckProductDto> GetCheckStockLog(HashMap<String,Object> map) {
        SQLParameter parameter = new SQLParameter(true);
        StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append(" select cr.checkstatus as status, ");
		sqlBuilder.append(" cr.iHandelMethod as iHandelMethod, ");
		sqlBuilder.append(" cr.tagStatus as tagStatus, ");
		sqlBuilder.append(" cr.checkID as checkId, ");
		sqlBuilder.append(" cr.checkLocationID as location_id_scan, ");
		sqlBuilder.append(" cr.storkLocationID as location_id, ");
		sqlBuilder.append(" rfid.rfid_code as rfid_code, ");
		sqlBuilder.append(" rfid.f_stock_count as f_stock_count, ");
		sqlBuilder.append(" rfid.f_move_count as f_move_count, ");
		sqlBuilder.append(" rfid.f_out_count as f_out_count, ");
		sqlBuilder.append(" rfid.id as id, ");
		sqlBuilder.append(" rfid.product_id as product_id, ");
		sqlBuilder.append(" p.productName as product_name, ");
		sqlBuilder.append(" p.productClass as productClass, ");
		sqlBuilder.append(" p.code as product_code, ");
		sqlBuilder.append(" rfid.product_sku_id as product_sku_id, ");
		sqlBuilder.append(" rfid.product_sku_name as product_sku_name, ");
		sqlBuilder.append(" u.unitName as unit_name, ");
		sqlBuilder.append(" u.unitGroup_name as stock_unit_name, ");
		sqlBuilder.append(" cr.storkLocationID as location_id, ");
		sqlBuilder.append(" location.locationName as locationName, ");
		sqlBuilder.append(" location.locationCode as locationCode, ");
		sqlBuilder.append(" rfid.rfid_status as rfid_status, ");
		sqlBuilder.append(" rfid.f_out_count as f_out_count, ");
		sqlBuilder.append(" rfid.f_move_count as f_move_count, ");
		sqlBuilder.append(" rfid.batchno as batchno, ");
		sqlBuilder.append(" rfid.invalidDate as invalidDate, ");
		sqlBuilder.append(" rfid.productDate as productDate ");
		sqlBuilder.append(" from dxq_checkresult cr ");
		sqlBuilder.append(" inner join dxq_checkstockarea csa on cr.checkAreaID = csa.id ");
		sqlBuilder.append(" LEFT JOIN dxq_location location on location.id = cr.storkLocationID ");
		sqlBuilder.append(" left join dxq_coderfid rfid on rfid.id = cr.coderfID ");
		sqlBuilder.append(" left join dxqyy_product p on cr.product_id = p.id ");
		sqlBuilder.append(" left join dxqyy_unit u on p.stockUnit = u.id ");
		sqlBuilder.append(" where cr.checkID = ? ");
		parameter.addParam(map.get("checkId"));
		if (map.get("Ids") != null) {
			if (CollectionUtil.isNotEmpty(map.get("Ids"))) {
				sqlBuilder.append(" and cr.checkLocationID in ");
				sqlBuilder.append(" ( ");
				int index = 0;
				String separator0 = " , ";
				//迭代器强转风险！
				Iterator<?> iterableIterator = ((Iterable<?>) map.get("Ids")).iterator();
				while (iterableIterator.hasNext()) {
					Object id = iterableIterator.next();
					sqlBuilder.append(" ? ");
					parameter.addParam(id);
					if (iterableIterator.hasNext()) {
						sqlBuilder.append(separator0);
					}
					index++;
				}
				sqlBuilder.append(" ) ");
			}
		}
		sqlBuilder.append(" and rfid.f_stock_count > 0 ");
		sqlBuilder.append(" and cr.dr = 0 ");
		sqlBuilder.append(" order by location.id; ");

        
        String sql = sqlBuilder.toString();
        ResultSetProcessor processor = new AutoBeanListProcessor(new CheckProductDto());
        return (List<CheckProductDto>) ymsJdbcApi.queryForList(sql, parameter, processor);
    }


    public String SelCheckStatusByid(String checkID, String tenant_id) throws Exception {
        SQLParameter parameter = new SQLParameter(true);
        
        String sql = "select iStatus from  dxq_checkstock  where id=?  and ytenant_id=? and `dr`=0;\n";
        parameter.addParam(checkID);
		parameter.addParam(tenant_id);

        ResultSetProcessor processor = new ColumnProcessor(String.class);
        return (String) ymsJdbcApi.queryForObject(sql, parameter, processor);
    }


    public List<checkstockMainOrder> GetCheckListNotLocation(String checkCode, String locationID, String checkTypeFilter, int page, int pageSize, String tenant_id) {
        SQLParameter parameter = new SQLParameter(true);
        
        String sql = "SELECT * from dxq_checkstock as c where c.cCheckCode like ? and c.checkType like ? and c.iStatus IN (0,1)  and c.ytenant_id=? order by c.id limit ?,?;\n";
        parameter.addParam(checkCode);
		parameter.addParam(checkTypeFilter);
		parameter.addParam(tenant_id);
		parameter.addParam(page);
		parameter.addParam(pageSize);

        ResultSetProcessor processor = new AutoBeanListProcessor(new checkstockMainOrder());
        return (List<checkstockMainOrder>) ymsJdbcApi.queryForList(sql, parameter, processor);
    }


    public Integer GetCheckListNotLocationToTotal(String checkCode, String locationID, String checkTypeFilter, String tenant_id) {
        SQLParameter parameter = new SQLParameter(true);
        
        String sql = "select count(*) from ( SELECT Count(*) from dxq_checkstock as c where c.cCheckCode like ?  and c.ytenant_id=? and c.checkType like ? and c.iStatus IN (0,1)) as t and c.dr=0;\n";
        parameter.addParam(checkCode);
		parameter.addParam(tenant_id);
		parameter.addParam(checkTypeFilter);

        ResultSetProcessor processor = new ColumnProcessor(Integer.class);
        return (Integer) ymsJdbcApi.queryForObject(sql, parameter, processor);
    }


    public List<checkStockListDto> GetCheckListLocation(HashMap<String,Object> map) {
        SQLParameter parameter = new SQLParameter(true);
        StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append(" select c.id, ");
		sqlBuilder.append(" c.cCheckCode, ");
		sqlBuilder.append(" c.checkNo, ");
		sqlBuilder.append(" c.checkType, ");
		sqlBuilder.append(" c.sourceType, ");
		sqlBuilder.append(" c.iStatus, ");
		sqlBuilder.append(" c.warehousename, ");
		sqlBuilder.append(" c.warehouseId, ");
		sqlBuilder.append(" w.code as warehouseCode , ");
		sqlBuilder.append(" c.create_time, ");
		sqlBuilder.append(" c.userid, ");
		sqlBuilder.append(" c.userid_operator, ");
		sqlBuilder.append(" c.IsFupan, ");
		sqlBuilder.append(" c.cRemark, ");
		sqlBuilder.append(" c2.locationID as locationId, ");
		sqlBuilder.append(" c2.locationName as locationName, ");
		sqlBuilder.append(" c2.locationCode as locationCode ");
		sqlBuilder.append(" from dxq_checkstock as c ");
		sqlBuilder.append(" inner join dxq_checkstockarea c2 on c.id = c2.dxq_checkstock_id ");
		sqlBuilder.append(" inner join dxqyy_warehouse as w on c.warehouseId = w.id ");
		sqlBuilder.append(" where 1=1 ");
		if (CollectionUtil.isNotEmpty(map.get("locationIds"))) {
			sqlBuilder.append(" and c2.locationID IN ");
			sqlBuilder.append(" ( ");
			int index = 0;
			String separator0 = " , ";
			//迭代器强转风险！
			Iterator<?> iterableIterator = ((Iterable<?>) map.get("locationIds")).iterator();
			while (iterableIterator.hasNext()) {
				Object id = iterableIterator.next();
				sqlBuilder.append(" ? ");
				parameter.addParam(id);
				if (iterableIterator.hasNext()) {
					sqlBuilder.append(separator0);
				}
				index++;
			}
			sqlBuilder.append(" ) ");
		}

		if (map.get("checkCode") != null && !"".equals(map.get("checkCode"))) {
			sqlBuilder.append(" and c.cCheckCode like concat('%',?,'%') ");
			parameter.addParam(map.get("checkCode"));
		}

		if (CollectionUtil.isNotEmpty(map.get("warehouseIds"))) {
			sqlBuilder.append(" and c.warehouseId IN ");
			sqlBuilder.append(" ( ");
			int index = 0;
			String separator1 = " , ";
			//迭代器强转风险！
			Iterator<?> iterableIterator = ((Iterable<?>) map.get("warehouseIds")).iterator();
			while (iterableIterator.hasNext()) {
				Object item = iterableIterator.next();
				sqlBuilder.append(" ? ");
				parameter.addParam(item);
				if (iterableIterator.hasNext()) {
					sqlBuilder.append(separator1);
				}
				index++;
			}
			sqlBuilder.append(" ) ");
		}

		if (map.get("warehouseId") != null && !"".equals(map.get("warehouseId"))) {
			sqlBuilder.append(" and c.warehouseId = ? ");
			parameter.addParam(map.get("warehouseId"));
		}
		sqlBuilder.append(" and c.iStatus IN (0, 1) ");
		sqlBuilder.append(" and c.dr = 0 ");

		if (map.get("checkType") != null && !"".equals(map.get("checkType"))) {
			sqlBuilder.append(" and c.checkType = ? ");
			parameter.addParam(map.get("checkType"));
		}

		sqlBuilder.append(" group by c.id ");
		sqlBuilder.append(" order by c.id ");
		sqlBuilder.append(" limit ? ,? ");
		parameter.addParam(map.get("page"));
		parameter.addParam(map.get("pageSize"));

        
        String sql = sqlBuilder.toString();
        ResultSetProcessor processor = new AutoBeanListProcessor(new checkStockListDto());
        return (List<checkStockListDto>) ymsJdbcApi.queryForList(sql, parameter, processor);
    }

}
